SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

--票据贴现 1=财,2=转,3=银
create PROCEDURE [dbo].[sp_jiuqi_voucher_note] @billid INT,@isignseq INT OUTPUT,@csign VARCHAR(100) OUTPUT

AS

DECLARE @type INT,@inctype INT,@sfkid INT,@bankid INT,@compid INT,@ysyftype INT,@bizDate smalldatetime
SELECT @type=TYPE,@inctype=inctype,@sfkid=sfkid,@bankid=bankid,@compid=dbo.gf_sys_cwcompid_by_compid(compid),@ysyftype=ysyftype,@bizDate=zddt FROM t_cw_pj_bill WHERE billid=@billid

DECLARE @note VARCHAR(255),@je NUMERIC(18,2),@fyje NUMERIC(18,2)

DECLARE @ccode1 VARCHAR(100),@ccname1 VARCHAR(100),@je1 NUMERIC(18,2)
DECLARE @ccode2 VARCHAR(100),@ccname2 VARCHAR(100),@je2 NUMERIC(18,2)
DECLARE @ccode3 VARCHAR(100),@ccname3 VARCHAR(100),@je3 NUMERIC(18,2)

DECLARE @jiuqitype INT

SELECT @jiuqitype=jiuqitype FROM t_cw_bank WHERE bankid=@bankid

SELECT @isignseq=dbo.gf_cw_sys_isignseq(dbo.gf_sys_cwcompid_by_compid(@compid),@jiuqitype)
SELECT @csign=dbo.gf_cw_sys_csign(dbo.gf_sys_cwcompid_by_compid(@compid),@isignseq)

SELECT @je=SUM(b.je) FROM t_cw_pj_bill_dtl a,t_cw_pj b WHERE a.billid=@billid AND a.pjid=b.pjid
SELECT @fyje=SUM(je) FROM t_cw_sfk WHERE sfkid=@sfkid

if @type=2
BEGIN
	SELECT @note='收'+(SELECT NAME FROM t_cw_bank WHERE bankid=@bankid)+'银行贴现款'	

	if isnull(@sfkid,0)=0 --无贴现费用
	BEGIN
		SELECT @note='收'+(SELECT NAME FROM t_cw_bank WHERE bankid=@bankid)+'银行托收款'	
	END

	SELECT @ccode1=(SELECT jiuqi_saving_code FROM t_cw_bank WHERE bankid=@bankid)
	SELECT @ccname1='银行存款/'+(SELECT NAME FROM t_cw_bank WHERE bankid=@bankid) 
	SELECT @je1=isnull(@je,0)-isnull(@fyje,0)

	
	SELECT @ccode2=(
		SELECT C.k3code FROM t_cw_sfk a 
			INNER JOIN t_cw_sfk_dtl b ON a.sfkid=b.sfkid 
			INNER JOIN t_cw_feetype C ON b.ftid=C.ftid
		WHERE a.sfkid=@sfkid)
	SELECT @ccname2=dbo.gf_cw_sys_code_name(@compid,@ccode2)
	SELECT @je2=isnull(@fyje,0)

	SELECT @ccode3=(SELECT code FROM t_jiuqi_common_subject_code WHERE company_id=@compid AND code_id=(CASE WHEN @ysyftype=1 THEN (CASE @inctype WHEN 5 THEN 600 WHEN 6 THEN 601 END) WHEN @ysyftype=2 THEN 700 END))
	SELECT @ccname3=(SELECT subject_name FROM t_jiuqi_common_subject WHERE code_id=(CASE WHEN @ysyftype=1 THEN (CASE @inctype WHEN 5 THEN 600 WHEN 6 THEN 601 END) WHEN @ysyftype=2 THEN 700 END))
	SELECT @je3=isnull(@je,0)
END

if @type=3
BEGIN
	SELECT @note='还'+(SELECT NAME FROM t_cw_bank WHERE bankid=@bankid)+'银行银承到期款'

	SELECT @ccode1=(SELECT code FROM t_jiuqi_common_subject_code WHERE company_id=@compid AND code_id=700)
	SELECT @ccname1=(SELECT subject_name FROM t_jiuqi_common_subject WHERE code_id=700)
	SELECT @je1=isnull(@je,0)

	SELECT @ccode2=(
		SELECT C.k3code FROM t_cw_sfk a 
			INNER JOIN t_cw_sfk_dtl b ON a.sfkid=b.sfkid 
			INNER JOIN t_cw_feetype C ON b.ftid=C.ftid
		WHERE a.sfkid=@sfkid)
	SELECT @ccname2=dbo.gf_cw_sys_code_name(@compid,@ccode2)
	SELECT @je2=isnull(@fyje,0)

	SELECT @ccode3=(SELECT jiuqi_saving_code FROM t_cw_bank WHERE bankid=@bankid)
	SELECT @ccname3='银行存款/'+(SELECT NAME FROM t_cw_bank WHERE bankid=@bankid) 
	SELECT @je3=isnull(@je,0)+isnull(@fyje,0)
END


SELECT 
	note=@note,
	ccode=@ccode1,
	ccname=@ccname1,
	md=@je1,
	mc=0.00,
	ismd=1,
	ismc=0,
	ccus_id=NULL,
	csup_id=NULL,
	isdept=0,
	isproc=(CASE WHEN @type=2 THEN 1 ELSE 0 END),
	bizDate=@bizDate

UNION ALL

SELECT 
	note=@note,
	ccode=@ccode2,
	ccname=@ccname2,
	md=@je2,
	mc=0.00,
	ismd=1,
	ismc=0,
	ccus_id=NULL,
	csup_id=NULL,
	isdept=0,
	isproc=0,
	bizDate=@bizDate
WHERE @je2<>0 

UNION ALL

SELECT
	note=@note,
	ccode=@ccode3,
	ccname=@ccname3,
	md=0.00,
	mc=@je3,
	ismd=0,
	ismc=1,
	ccus_id=NULL,
	csup_id=NULL,
	isdept=0,
	isproc=(CASE WHEN @type=3 THEN 1 ELSE 0 END),
	bizDate=@bizDate
















